﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SCRUM.Core.BusinessObjects;
using System.Data.Common;

namespace SCRUM.Core.DataProviders
{
    /// <summary>
    /// Privider for comunication with the database
    /// </summary>
    public partial class DBDataProvider : DataProvider
	{
		/// <summary>
		/// Gets the work progress for user.
		/// </summary>
		/// <param name="userID">The user ID.</param>
		/// <param name="from">From.</param>
		/// <param name="to">To.</param>
		/// <returns></returns>
        public override IList<WorkProgress> GetProgressForUser(int userID, DateTime from, DateTime to)
        {
            IList<WorkProgress> output = new List<WorkProgress>();
            try
            {
                connection.Open();

                DbParameter dbUserID = CreateParameter("userID", userID);
				DbParameter dbFrom = CreateParameter("from", from);
				DbParameter dbTo = CreateParameter("to", to);

                DbDataReader data = GetGrid("workProgress/GetProgressForUser", dbUserID, dbFrom, dbTo);

                while (data.Read())
                {
                    output.Add(GetWorkProgress(data));
                }
            }
            finally
            {
                connection.Close();
            }
            return output;
        }

        /// <summary>
        /// Adds the work progress.
        /// </summary>
        /// <param name="userID">The user ID.</param>
        /// <param name="taskID">The task ID.</param>
        /// <param name="date">The date.</param>
        /// <param name="storyPoints">The story points.</param>
        /// <param name="note">The note.</param>
        public override void AddWorkProgress(int userID, int taskID, DateTime date, double storyPoints, string note)
        {
            Task task = DAO.DAOTask.GetTaskByID(taskID);
            int sprintID = task.BacklogItem.UserStory.Sprint.ID;
            int taskStateID = task.State.ID;

            try
            {
                connection.Open();

                DbParameter dbUserID = CreateParameter("userID", userID);
                DbParameter dbTaskID = CreateParameter("taskID", taskID);
                DbParameter dbDate = CreateParameter("date", date);
                DbParameter dbStoryPoints = CreateParameter("storyPoints", storyPoints);
                DbParameter dbNote = CreateParameter("note", note);
                DbParameter dbSprintID = CreateParameter("sprintID", sprintID);
                DbParameter dbTaskStateID = CreateParameter("taskStateID", taskStateID);

                ExecuteNonQuery("workProgress/AddWorkProgress", dbUserID, dbTaskID, dbDate, dbStoryPoints, dbNote, dbSprintID, dbTaskStateID);
            }
            finally
            {
                connection.Close();
            }
            updateProgressForEntities(storyPoints, taskID);
        }

		/// <summary>
		/// Deletes the work progress.
		/// </summary>
		/// <param name="progressID">The progress ID.</param>
		public override void DeleteWorkProgress(int progressID)
		{
            WorkProgress toDelete = GetProgressByID(progressID);
			try
			{
				connection.Open();
				DbParameter workProgressID = CreateParameter("progressID", progressID);

				ExecuteNonQuery("workProgress/DeleteWorkProgress", workProgressID);
			}
			finally
			{
				connection.Close();
			}
            updateProgressForEntities(-toDelete.StoryPoints, toDelete.AssociatedTask.ID);
		}

        /// <summary>
        /// Updates all entities in hierarchy. Increase progress of all enities in hierarchy that 
        /// is task involved in.
        /// </summary>
        /// <param name="storyPoint">Progress in number of story points </param>
        /// <param name="taskID">ID of task</param>
        private void updateProgressForEntities(double storyPoint, int taskID)
        {
            try
            {
                connection.Open();
                DbParameter dbStoryPoints = CreateParameter("storyPoints", storyPoint);
                DbParameter dbTaskID = CreateParameter("taskID", taskID);

                ExecuteNonQuery("workProgress/UpdateProgressForEntities", dbStoryPoints, dbTaskID);
            }
            finally
            {
                connection.Close();
            }
        }

		/// <summary>
		/// Edits the work progress.
		/// </summary>
		/// <param name="workProgress">The work progress.</param>
		public override void EditWorkProgress(WorkProgress workProgress)
		{
            WorkProgress oldWorkProgress = GetProgressByID(workProgress.ID);
			try
			{
				connection.Open();

				DbParameter dbID = CreateParameter("id", workProgress.ID);
				DbParameter dbDate = CreateParameter("date", workProgress.Date);
				DbParameter dbStoryPoints = CreateParameter("storyPoints", workProgress.StoryPoints);
				DbParameter dbNote = CreateParameter("note", workProgress.Note);

				ExecuteNonQuery("workProgress/EditWorkProgress", dbID, dbDate, dbStoryPoints, dbNote);
			}
			finally
			{
				connection.Close();
			}
            updateProgressForEntities(workProgress.StoryPoints - oldWorkProgress.StoryPoints, oldWorkProgress.AssociatedTask.ID);
		}

		/// <summary>
		/// Gets the work progress.
		/// </summary>
		/// <param name="data">The data.</param>
		/// <returns>Wodk progress.</returns>
        private WorkProgress GetWorkProgress(DbDataReader data)
        {
            return new WorkProgress(
                                    (int)data["ID"],
                                    (int)data["USER_ID"],
                                    (int)data["TASK_ID"],
                                    (DateTime)data["DATE"],
                                    (double)data["STORY_POINTS"],
                                    GetStringByColumn(data, "NOTE")
                                    );
        }

		/// <summary>
		/// Gets the progress by ID.
		/// </summary>
		/// <param name="progressID">The progress ID.</param>
		/// <returns>Work progress with specified ID.</returns>
		public override WorkProgress GetProgressByID(int progressID)
		{
			WorkProgress workProgress = null;
			try
			{
				connection.Open();
				DbParameter workProgressID = CreateParameter("progressID", progressID);

				DbDataReader data = GetGrid("workProgress/GetProgressByID", workProgressID);

				if (data.Read())
				{
					workProgress = GetWorkProgress(data);
				}
			}
			finally
			{
				connection.Close();
			}
			return workProgress;
		}
	}
}
